/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package diet.dao;

import diet.dto.DishDTO;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author DungTT
 */
public class DishDAO {

    public boolean add(DishDTO dish) {
        Connection con = null;
        PreparedStatement stm = null;
        try {
            con = DAO.ConnectWithDriver();
            stm = con.prepareStatement("INSERT INTO [Dish](DishName,DishTypeId,Calories,ImageURL,Time) VALUES(?,?,?,?,?)");
            stm.setString(1, dish.getDishName());
            stm.setInt(2, dish.getDishTypeId());
            stm.setInt(3, dish.getCalories());
            stm.setString(4, dish.getImageURL());
            stm.setString(5, dish.getTime());
            stm.executeUpdate();
            return true;
        } catch (SQLException ex) {
            System.out.println("SQLException Error: " + ex.getMessage());
            return false;
        } finally {
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException ex) {
                    System.out.println("Connection error: " + ex.getMessage());
                }
            } else if (stm != null) {
                try {
                    stm.close();
                } catch (SQLException ex) {
                    System.out.println("Statement error " + ex.getMessage());
                }
            }
        }
    }

    public boolean delete(int DishID) {
        Connection con = null;
        PreparedStatement stm = null;
        try {
            con = DAO.ConnectWithDriver();
            stm = con.prepareStatement("DELETE FROM [Dish] WHERE DishID=? ");
            stm.setInt(1, DishID);
            stm.executeUpdate();
            return true;
        } catch (SQLException ex) {
            System.out.println("SQLException Error: " + ex.getMessage());
            return false;
        } finally {
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException ex) {
                    System.out.println("Connection error: " + ex.getMessage());
                }
            } else if (stm != null) {
                try {
                    stm.close();
                } catch (SQLException ex) {
                    System.out.println("Statement error " + ex.getMessage());
                }
            }
        }
    }

    public boolean update(DishDTO dish) {
        Connection con = null;
        PreparedStatement stm = null;
        try {
            con = DAO.ConnectWithDriver();
            stm = con.prepareStatement("UPDATE [Dish] SET DishID =?,DishName=?,DishTypeId=?,Calories=?,ImageURL=?,Time=?"
                    + " WHERE DishID=?");
            stm.setString(1, dish.getDishName());
            stm.setInt(2, dish.getDishTypeId());
            stm.setInt(3, dish.getCalories());
            stm.setString(4, dish.getImageURL());
            stm.setString(5, dish.getTime());
            stm.executeUpdate();
            return true;
        } catch (SQLException ex) {
            System.out.println("SQLException Error: " + ex.getMessage());
            return false;
        } finally {
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException ex) {
                    System.out.println("Connection error: " + ex.getMessage());
                }
            } else if (stm != null) {
                try {
                    stm.close();
                } catch (SQLException ex) {
                    System.out.println("Statement error " + ex.getMessage());
                }
            }
        }
    }
    //NhuNHU
    //getDish by dishID
    public DishDTO getDish (int dishID) {

        Connection con = null;
        PreparedStatement st = null;
        DishDTO dishDTO = new DishDTO();
        try{
            con = DAO.ConnectWithDriver();
            st = con.prepareStatement("SELECT * FROM [Dish] WHERE DishID = ?");
            st.setInt(1, dishID);

            ResultSet rs = st.executeQuery();

            while(rs.next()){
                dishDTO.setDishID(dishID);
                dishDTO.setDishName(rs.getNString("DishName"));
                dishDTO.setDishTypeId(rs.getInt("DishTypeId"));
                dishDTO.setImageURL(rs.getNString("ImageURL"));
                dishDTO.setCalories(rs.getInt("Calories"));
                dishDTO.setTime(rs.getNString("Time"));
            }
            rs.close();
            con.close();
            return dishDTO;
        }catch (SQLException ex){
            System.out.println("SQLException Error: " + ex.getMessage());
            return null;
        }
    }
    //VuongHTT
    //getListDish by Time
    public List<DishDTO> getListDishByTime (String time) {

        Connection con = null;
        PreparedStatement st = null;
        List<DishDTO> dishes = new ArrayList<DishDTO>();
        
        try{
            con = DAO.ConnectWithDriver();
            st = con.prepareStatement("SELECT * FROM [Dish] WHERE Time LIKE ? ");
            st.setString(1,"%" + time + "%");
            ResultSet rs = st.executeQuery();
            while(rs.next()){
                DishDTO dishDTO = new DishDTO();
                dishDTO.setDishID(rs.getInt("DishID"));
                dishDTO.setDishName(rs.getNString("DishName"));
                dishDTO.setDishTypeId(rs.getInt("DishTypeId"));
                dishDTO.setImageURL(rs.getNString("ImageURL"));
                dishDTO.setCalories(rs.getInt("Calories"));
                dishDTO.setTime(rs.getNString("Time"));
                dishes.add(dishDTO);
            }
            rs.close();
            con.close();
            return dishes;
        }catch (SQLException ex){
            System.out.println("SQLException Error: " + ex.getMessage());
            return null;
        }
    }
}
